package com.zh.work.util.plateform;

import com.zh.work.entity.virtual.VirtualRechargeTrans;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * code is far away from bug with the animal protecting
 * ┏┓　　　┏┓
 * ┏┛┻━━━┛┻┓
 * ┃　　　　　　　┃
 * ┃　　　━　　　┃
 * ┃　┳┛　┗┳　┃
 * ┃　　　　　　　┃
 * ┃　　　┻　　　┃
 * ┃　　　　　　　┃
 * ┗━┓　　　┏━┛
 * 　　┃　　　┃神兽保佑
 * 　　┃　　　┃代码无BUG！
 * 　　┃　　　┗━━━┓
 * 　　┃　　　　　　　┣┓
 * 　　┃　　　　　　　┏┛
 * 　　┗┓┓┏━┳┓┏┛
 * 　　　┃┫┫　┃┫┫
 * 　　　┗┻┛　┗┻┛
 *
 * @Description: 操作上传Excel文件工具类
 * @Author: Li.HuiNeng
 * @Create in: 2020-02-11 15:45
 * @Version: 1.0.1
 */
public class OfficeUtils {

    protected static final Logger logger = LoggerFactory.getLogger(OfficeUtils.class);

    public static Map<Integer, Map<Integer, Object>> readExcelContent(MultipartFile file) throws Exception {
        Map<Integer, Map<Integer, Object>> content = new HashMap<Integer, Map<Integer, Object>>();
        // 上传文件名
        Workbook wb = getWb(file);
        if (wb == null) {
            throw new Exception("上传文件为空!");
        }
        Sheet sheet = wb.getSheetAt(0);
        // 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
        // 26 27
        // 序号 商户简称 商户全称 商户类别 机构代码 营业执照 法人姓名 法人证件号 联系人 电子邮箱 固定电话 手机号码 所在省份 所在城市
        // 详细地址 T+1费率 T+1封顶 行业类型 MCC 结算类型 结算账户 账户类型 账户户名 开户行行号 业务类型 T+0费率 T+0封顶
        // 商户级别 客户号
        int maxRow = sheet.getPhysicalNumberOfRows();
        int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        if (maxRow <= 2) {
            throw new Exception("Excel文件中无数据,行数不能小于1");
        } else if (maxRow >= 4000) {
            throw new Exception("Excel表中数据最多为2000条");
        }
        if (maxColumn != 7) {
            throw new Exception("Excel文件中的格式有误,必须是7列数据");
        }
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            // 第一列为序号，不需要
            int j = 1;
            Map<Integer, Object> cellValue = new HashMap<Integer, Object>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));
                cellValue.put(j, obj);
                j++;
            }
            content.put(i, cellValue);
        }
        return content;
    }

    //根据Cell类型设置数据
    private static Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        switch (cell.getCellType()) {
            case NUMERIC:
                //数值类型又具体区分日期类型，单独处理
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    cellvalue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                } else {
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case FORMULA: {
                cellvalue = cell.getDateCellValue();
                break;
            }
            case STRING:
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            default:
                cellvalue = "";
        }
        return cellvalue;
    }

    private static Workbook getWb(MultipartFile mf) {
        String filepath = mf.getOriginalFilename();
        String ext = filepath.substring(filepath.lastIndexOf("."));
        Workbook wb = null;
        try {
            InputStream is = mf.getInputStream();
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
        return wb;
    }

    // 根据表格组装对应的实体信息
    public static Map<String, Object> getVirtualCardListByMap(Map<Integer, Map<Integer, Object>> map) throws ParseException {
        Map<String, Object> resultMap = new HashMap<>();
        VirtualRechargeTrans rechargeTrans = new VirtualRechargeTrans();
        List<VirtualRechargeTrans> list = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf1 = new SimpleDateFormat("HH:mm:ss");
        StringBuffer sb = new StringBuffer();
        for (Integer i : map.keySet()) {
            Map<Integer, Object> map1 = map.get(i);
            if (map1.get(1) == null || map1.get(2) == null || map1.get(3) == null || map1.get(4) == null || map1.get(5) == null || map1.get(6) == null) {
                sb.append(i + ",");
                continue;
            }
            rechargeTrans.setAddTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
            Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(String.valueOf(map1.get(1)));
            rechargeTrans.setTransDate(sdf.format(date));
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            int hour = calendar.get(Calendar.HOUR_OF_DAY);
            int minute = calendar.get(Calendar.MINUTE);
            int millisecond = calendar.get(Calendar.SECOND);
            rechargeTrans.setTransTime(hour + ":" + minute + ":" + millisecond);
            rechargeTrans.setBranchNo(map1.get(2).toString());
            rechargeTrans.setBranchName(map1.get(3).toString());
            rechargeTrans.setMerchNo(map1.get(4).toString());
            rechargeTrans.setMerchName(map1.get(5).toString());
            rechargeTrans.setAmount(Float.valueOf(map1.get(6).toString()));
            list.add(rechargeTrans);
        }
        if (sb.length() != 0) {
            resultMap.put("errNo", "行号为: [" + (sb.length() - 1) + "】数据不完善，请检查之后再次添加");
        }
        resultMap.put("result", list);
        return resultMap;
    }
}
